﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using Model;
using System.Data;
using System.Globalization;

namespace DataAccess
{
    public class DataBaseAccess
    {
        private const string connect = "Data Source=.\\sqlexpress; Initial Catalog=Brigada; Integrated Security=true";

        public DataBaseAccess()
        {

        }

        public static bool RegisterEmployee(Functionary employee)
        {
            bool exit = true;
            using (SqlConnection SQL_Connection = new SqlConnection(connect))
            {
                try
                {
                    SqlCommand cmd = SQL_Connection.CreateCommand();
                    cmd.CommandText = "INSERT INTO [Employee] (CI,Name,PaternalName,MaternalName,BirthDate,BirthPlace,WorkingRole,Rank,CivilStatus,BloodType,Status,JoinDate,Address,Phone,Gener) VALUES (@ci,@Name,@PaternalName,@MaternalName,@BirthDate,@BirthPlace,@WorkingRole,@Rank,@CivilStatus,@BloodType,@Status,@JoinDate,@Address,@Phone,@Gener)";
                    cmd.Parameters.AddWithValue("ci", employee.CI);
                    cmd.Parameters.AddWithValue("Address", employee.Address);
                    cmd.Parameters.AddWithValue("BirthDate", employee.BirthDate);
                    cmd.Parameters.AddWithValue("BirthPLace", employee.BirthPLace);
                    cmd.Parameters.AddWithValue("BloodType", employee.BloodType);
                    cmd.Parameters.AddWithValue("CivilStatus", employee.CivilStatus);
                    cmd.Parameters.AddWithValue("Gener", employee.Gener);
                    cmd.Parameters.AddWithValue("JoinDate", employee.JoinDate);
                    cmd.Parameters.AddWithValue("MaternalName", GetDataValue(employee.MaternalName));
                    cmd.Parameters.AddWithValue("Name", employee.Name);
                    cmd.Parameters.AddWithValue("PaternalName", employee.PaternalName);
                    cmd.Parameters.AddWithValue("Rank", employee.Rank);
                    cmd.Parameters.AddWithValue("WorkingRole", employee.WorkingRole);
                    cmd.Parameters.AddWithValue("Status", false);
                    cmd.Parameters.AddWithValue("Phone", employee.Phone);
                    SQL_Connection.Open();
                    cmd.ExecuteNonQuery();
                    CreateContract(employee.CI, employee.JoinDate);
                }
                catch (SqlException)
                {
                    exit = false;
                }
            }
            return exit;
        }

        public static object GetDataValue(object value)
        {
            if (value == null)
            {
                return DBNull.Value;
            }

            return value;
        }

        public static bool UpdateEmployee(Functionary employee)
        {
            bool exit = true;
            using (SqlConnection SQL_Connection = new SqlConnection(connect))
            {
                try
                {
                    SqlCommand cmd = SQL_Connection.CreateCommand();
                    cmd.CommandText = "UPDATE [Employee] SET Name=@Name,PaternalName=@PaternalName,MaternalName=@MaternalName,BirthDate=@BirthDate,BirthPlace=@BirthPlace,WorkingRole=@WorkingRole,Rank=@Rank,CivilStatus=@CivilStatus,BloodType=@BloodType,Status=@Status,JoinDate=@JoinDate,Address=@Address, Phone=@Phone, Gener=@Gener WHERE CI=@Ci";
                    cmd.Parameters.AddWithValue("ci", employee.CI);
                    cmd.Parameters.AddWithValue("Address", employee.Address);
                    cmd.Parameters.AddWithValue("BirthDate", employee.BirthDate);
                    cmd.Parameters.AddWithValue("BirthPLace", employee.BirthPLace);
                    cmd.Parameters.AddWithValue("BloodType", employee.BloodType);
                    cmd.Parameters.AddWithValue("CivilStatus", employee.CivilStatus);
                    cmd.Parameters.AddWithValue("Gener", employee.Gener);
                    cmd.Parameters.AddWithValue("JoinDate", employee.JoinDate);
                    cmd.Parameters.AddWithValue("MaternalName", employee.MaternalName);
                    cmd.Parameters.AddWithValue("Name", employee.Name);
                    cmd.Parameters.AddWithValue("PaternalName", employee.PaternalName);
                    cmd.Parameters.AddWithValue("Rank", employee.Rank);
                    cmd.Parameters.AddWithValue("WorkingRole", employee.WorkingRole);
                    cmd.Parameters.AddWithValue("Status", employee.Status);
                    cmd.Parameters.AddWithValue("Phone", employee.Phone);
                    SQL_Connection.Open();
                    cmd.ExecuteNonQuery();
                    UpdateContract(employee.CI, employee.JoinDate);
                }
                catch (SqlException)
                {
                    exit = false;
                }
            }
            return exit;
        }

        public static bool ChangeEmployeeStatus(long ci, DateTime join, bool status)
        {
            bool exit = true;
            using (SqlConnection SQL_Connection = new SqlConnection(connect))
            {
                try
                {
                    DateTime date = new DateTime();
                    date = DateTime.Now;
                    SqlCommand cmd = SQL_Connection.CreateCommand();
                    cmd.CommandText = "UPDATE [Employee] SET Status=@Status, JoinDate=@JoinDate WHERE Ci=@Ci";
                    cmd.Parameters.AddWithValue("Ci", ci);
                    cmd.Parameters.AddWithValue("JoinDate", join);
                    cmd.Parameters.AddWithValue("Status", !status);
                    SQL_Connection.Open();
                    cmd.ExecuteNonQuery();
                    if (!status)
                        FinishContract(ci, join);
                    else
                        CreateContract(ci, join);
                }
                catch (SqlException)
                {
                    exit = false;
                }
            }
            return exit;
        }

        public static bool FinishContract(long ci, DateTime join)
        {
            bool exit = true;
            using (SqlConnection SQL_Connection = new SqlConnection(connect))
            {
                try
                {
                    DateTime date = new DateTime();
                    date = DateTime.Now;
                    SqlCommand cmd = SQL_Connection.CreateCommand();
                    cmd.CommandText = "UPDATE [Contract] SET DepartureDate=@DepartureDate WHERE CiEmployee=@Ci and JoinDate=@JoinDate";
                    cmd.Parameters.AddWithValue("Ci", ci);
                    cmd.Parameters.AddWithValue("DepartureDate", date);
                    cmd.Parameters.AddWithValue("JoinDate", join);
                    SQL_Connection.Open();
                    cmd.ExecuteNonQuery();
                }
                catch (SqlException)
                {
                    exit = false;
                }
            }
            return exit;
        }

        public static bool UpdateContract(long ci, DateTime join)
        {
            bool exit = true;
            using (SqlConnection SQL_Connection = new SqlConnection(connect))
            {
                try
                {
                    SqlCommand cmd = SQL_Connection.CreateCommand();
                    cmd.CommandText = "UPDATE [Contract] SET JoinDate=@JoinDate WHERE CiEmployee=@Ci and JoinDate=@JoinDate";
                    cmd.Parameters.AddWithValue("Ci", ci);
                    cmd.Parameters.AddWithValue("JoinDate", join);
                    SQL_Connection.Open();
                    cmd.ExecuteNonQuery();

                }
                catch (SqlException)
                {
                    exit = false;
                }
            }
            return exit;
        }

        public static bool CreateContract(long ci, DateTime join)
        {
            bool exit = true;
            using (SqlConnection SQL_Connection = new SqlConnection(connect))
            {
                try
                {
                    SqlCommand cmd = SQL_Connection.CreateCommand();
                    cmd.CommandText = "INSERT INTO [Contract] (CiEmployee,JoinDate) VALUES (@Ci,@JoinDate)";
                    cmd.Parameters.AddWithValue("Ci", ci);
                    cmd.Parameters.AddWithValue("JoinDate", join);
                    SQL_Connection.Open();
                    cmd.ExecuteNonQuery();

                }
                catch (SqlException)
                {
                    exit = false;
                }
            }
            return exit;
        }

        public static Functionary ReadEmployee(long ci)
        {
            Functionary employee = null;
            using (SqlConnection SQL_Connection = new SqlConnection(connect))
            {
                try
                {
                    SqlCommand cmd = SQL_Connection.CreateCommand();
                    cmd.CommandText = "SELECT * FROM [Employee] WHERE CI = @ci";
                    cmd.Parameters.AddWithValue("ci", ci);
                    SQL_Connection.Open();
                    SqlDataReader reader = cmd.ExecuteReader();
                    if (reader.HasRows)
                    {
                        if (reader.Read())
                        {
                            employee = new Functionary();
                            employee.Address = reader["Address"].ToString();
                            employee.BirthDate = Convert.ToDateTime(reader["BirthDate"]);
                            employee.BirthPLace = reader["BirthPLace"].ToString();
                            employee.BloodType = reader["BloodType"].ToString();
                            employee.CI = Convert.ToInt64(reader["CI"]);
                            employee.Phone = Convert.ToInt64(reader["Phone"]);
                            employee.CivilStatus = reader["CivilStatus"].ToString();
                            employee.Gener = reader["Gener"].ToString();
                            employee.JoinDate = Convert.ToDateTime(reader["JoinDate"]);
                            employee.MaternalName = reader["MaternalName"].ToString();
                            employee.PaternalName = reader["PaternalName"].ToString();
                            employee.Name = reader["Name"].ToString();
                            employee.Rank = reader["Rank"].ToString();
                            employee.Status = Convert.ToBoolean(reader["Status"]);
                            employee.WorkingRole = reader["WorkingRole"].ToString();
                        }
                    }
                }
                catch (SqlException)
                {

                }
            }
            return employee;
        }

        public static List<Functionary> GetAllemployees
        {
            get
            {
                List<Functionary> list;
                Functionary employee;
                SqlDataAdapter da = null;

                using (SqlConnection SQL_Connection = new SqlConnection(connect))
                {
                    string cmd = "SELECT * FROM [Employee]";
                    DataTable dataTable = null;
                    try
                    {
                        list = new List<Functionary>();
                        da = new SqlDataAdapter(cmd, SQL_Connection);
                        dataTable = new DataTable();
                        dataTable.Locale = CultureInfo.InvariantCulture;
                        SQL_Connection.Open();
                        // Fill the data table with select statement's query results:
                        int recordsAffected = da.Fill(dataTable);
                        if (recordsAffected > 0)
                        {
                            foreach (DataRow reader in dataTable.Rows)
                            {
                                employee = new Functionary();
                                employee.Address = reader["Address"].ToString();
                                employee.BirthDate = Convert.ToDateTime(reader["BirthDate"]);
                                employee.BirthPLace = reader["BirthPLace"].ToString();
                                employee.BloodType = reader["BloodType"].ToString();
                                employee.CI = Convert.ToInt64(reader["CI"]);
                                employee.Phone = Convert.ToInt64(reader["Phone"]);
                                employee.CivilStatus = reader["CivilStatus"].ToString();
                                employee.Gener = reader["Gener"].ToString();
                                employee.JoinDate = Convert.ToDateTime(reader["JoinDate"]);
                                employee.MaternalName = reader["MaternalName"].ToString();
                                employee.PaternalName = reader["PaternalName"].ToString();
                                employee.Name = reader["Name"].ToString();
                                employee.Rank = reader["Rank"].ToString();
                                employee.Status = Convert.ToBoolean(reader["Status"]);
                                employee.WorkingRole = reader["WorkingRole"].ToString();
                                list.Add(employee);
                            }
                        }
                    }
                    catch (SqlException)
                    {
                        return null;
                    }
                    finally
                    {
                        if (da != null)
                            da.Dispose();
                        if (dataTable != null)
                            dataTable.Dispose();
                    }
                }
                return list;
            }
        }

        public static List<Functionary> GetAllActiveEmployees
        {
            get
            {
                List<Functionary> list;
                Functionary employee;
                SqlDataAdapter da = null;

                using (SqlConnection SQL_Connection = new SqlConnection(connect))
                {
                    string cmd = "SELECT * FROM [Employee]";
                    DataTable dataTable = null;
                    try
                    {
                        list = new List<Functionary>();
                        da = new SqlDataAdapter(cmd, SQL_Connection);
                        dataTable = new DataTable();
                        dataTable.Locale = CultureInfo.InvariantCulture;
                        SQL_Connection.Open();
                        // Fill the data table with select statement's query results:
                        int recordsAffected = da.Fill(dataTable);
                        if (recordsAffected > 0)
                        {
                            foreach (DataRow reader in dataTable.Rows)
                            {
                                employee = new Functionary();
                                employee.Address = reader["Address"].ToString();
                                employee.BirthDate = Convert.ToDateTime(reader["BirthDate"]);
                                employee.BirthPLace = reader["BirthPLace"].ToString();
                                employee.BloodType = reader["BloodType"].ToString();
                                employee.CI = Convert.ToInt64(reader["CI"]);
                                employee.Phone = Convert.ToInt64(reader["Phone"]);
                                employee.CivilStatus = reader["CivilStatus"].ToString();
                                employee.Gener = reader["Gener"].ToString();
                                employee.JoinDate = Convert.ToDateTime(reader["JoinDate"]);
                                employee.MaternalName = reader["MaternalName"].ToString();
                                employee.PaternalName = reader["PaternalName"].ToString();
                                employee.Name = reader["Name"].ToString();
                                employee.Rank = reader["Rank"].ToString();
                                employee.Status = Convert.ToBoolean(reader["Status"]);
                                employee.WorkingRole = reader["WorkingRole"].ToString();
                                if (!employee.Status)
                                    list.Add(employee);
                            }
                        }
                    }
                    catch (SqlException)
                    {
                        return null;
                    }
                    finally
                    {
                        if (da != null)
                            da.Dispose();
                        if (dataTable != null)
                            dataTable.Dispose();
                    }
                }
                return list;
            }
        }

        public static List<Functionary> GetAllNonActiveEmployees
        {
            get
            {
                List<Functionary> list;
                Functionary employee;
                SqlDataAdapter da = null;

                using (SqlConnection SQL_Connection = new SqlConnection(connect))
                {
                    string cmd = "SELECT * FROM [Employee]";
                    DataTable dataTable = null;
                    try
                    {
                        list = new List<Functionary>();
                        da = new SqlDataAdapter(cmd, SQL_Connection);
                        dataTable = new DataTable();
                        dataTable.Locale = CultureInfo.InvariantCulture;
                        SQL_Connection.Open();
                        // Fill the data table with select statement's query results:
                        int recordsAffected = da.Fill(dataTable);
                        if (recordsAffected > 0)
                        {
                            foreach (DataRow reader in dataTable.Rows)
                            {
                                employee = new Functionary();
                                employee.Address = reader["Address"].ToString();
                                employee.BirthDate = Convert.ToDateTime(reader["BirthDate"]);
                                employee.BirthPLace = reader["BirthPLace"].ToString();
                                employee.BloodType = reader["BloodType"].ToString();
                                employee.CI = Convert.ToInt64(reader["CI"]);
                                employee.Phone = Convert.ToInt64(reader["Phone"]);
                                employee.CivilStatus = reader["CivilStatus"].ToString();
                                employee.Gener = reader["Gener"].ToString();
                                employee.JoinDate = Convert.ToDateTime(reader["JoinDate"]);
                                employee.MaternalName = reader["MaternalName"].ToString();
                                employee.PaternalName = reader["PaternalName"].ToString();
                                employee.Name = reader["Name"].ToString();
                                employee.Rank = reader["Rank"].ToString();
                                employee.Status = Convert.ToBoolean(reader["Status"]);
                                employee.WorkingRole = reader["WorkingRole"].ToString();
                                if (employee.Status)
                                    list.Add(employee);
                            }
                        }
                    }
                    catch (SqlException)
                    {
                        return null;
                    }
                    finally
                    {
                        if (da != null)
                            da.Dispose();
                        if (dataTable != null)
                            dataTable.Dispose();
                    }
                }
                return list;
            }
        }

        public static bool RegisterAudience(AudienceClass Audience)
        {
            bool exit = true;
            using (SqlConnection SQL_Connection = new SqlConnection(connect))
            {
                try
                {
                    SqlCommand cmd = SQL_Connection.CreateCommand();
                    cmd.CommandText = "INSERT INTO [Audience] (Id,IdComplaint,Date,Time,Status,CiEmployee,Report) VALUES (@id,@idComp,@date,@time,@status,@CiEmployee,@Report)";
                    cmd.Parameters.AddWithValue("id", Audience.Id);
                    cmd.Parameters.AddWithValue("date", Audience.Date);
                    cmd.Parameters.AddWithValue("time", Audience.Time);
                    cmd.Parameters.AddWithValue("idComp", Audience.IdComplaint);
                    cmd.Parameters.AddWithValue("status", Audience.Status);
                    cmd.Parameters.AddWithValue("CiEmployee", Audience.CiEmployee);
                    cmd.Parameters.AddWithValue("Report", Audience.Report);
                    SQL_Connection.Open();
                    cmd.ExecuteNonQuery();

                }
                catch (SqlException)
                {
                    exit = false;
                }
            }
            return exit;
        }

        public static bool ModifyAudience(AudienceClass Audience)
        {
            bool exit = true;
            using (SqlConnection SQL_Connection = new SqlConnection(connect))
            {
                try
                {
                    SqlCommand cmd = SQL_Connection.CreateCommand();
                    cmd.CommandText = "UPDATE [Audience] SET IdComplaint=@idComp,Date=@date,Time=@time,Status=@status,CiEmployee=@CiEmployee,Report=@Report WHERE Id = @id";
                    cmd.Parameters.AddWithValue("id", Audience.Id);
                    cmd.Parameters.AddWithValue("date", Audience.Date);
                    cmd.Parameters.AddWithValue("time", Audience.Time);
                    cmd.Parameters.AddWithValue("idComp", Audience.IdComplaint);
                    cmd.Parameters.AddWithValue("status", Audience.Status);
                    cmd.Parameters.AddWithValue("CiEmployee", Audience.CiEmployee);
                    cmd.Parameters.AddWithValue("Report", Audience.Report);
                    SQL_Connection.Open();
                    cmd.ExecuteNonQuery();

                }
                catch (SqlException)
                {
                    exit = false;
                }
            }
            return exit;
        }

        public static List<AudienceClass> GetAllAudiences(long complaint)
        {
            List<AudienceClass> list;
            AudienceClass audience;
            SqlDataAdapter da = null;

            using (SqlConnection SQL_Connection = new SqlConnection(connect))
            {
                SqlCommand cmd = SQL_Connection.CreateCommand();
                cmd.CommandText = "SELECT * FROM [Audience] WHERE IdComplaint = @complaint";
                cmd.Parameters.AddWithValue("complaint", complaint);
                DataTable dataTable = null;
                try
                {
                    list = new List<AudienceClass>();
                    da = new SqlDataAdapter(cmd);
                    dataTable = new DataTable();
                    dataTable.Locale = CultureInfo.InvariantCulture;
                    SQL_Connection.Open();
                    // Fill the data table with select statement's query results:
                    int recordsAffected = da.Fill(dataTable);
                    if (recordsAffected > 0)
                    {
                        foreach (DataRow reader in dataTable.Rows)
                        {
                            audience = new AudienceClass();
                            audience.Id = Convert.ToInt64(reader["Id"]);
                            audience.IdComplaint = Convert.ToInt64(reader["IdComplaint"]);
                            audience.CiEmployee = Convert.ToInt64(reader["CiEmployee"]);
                            audience.Date = Convert.ToDateTime(reader["Date"]);
                            audience.Time = reader["Time"].ToString();
                            audience.Status = Convert.ToBoolean(reader["Status"]);
                            audience.Report = reader["Report"].ToString();
                            
                            list.Add(audience);
                        }
                    }
                }
                catch (SqlException)
                {
                    return null;
                }
                finally
                {
                    if (da != null)
                        da.Dispose();
                    if (dataTable != null)
                        dataTable.Dispose();
                }
            }
            return list;
        }

        public static int NumberAudiences
        {
            get
            {
                SqlDataAdapter da = null;
                int number = -1;

                using (SqlConnection SQL_Connection = new SqlConnection(connect))
                {
                    SqlCommand cmd = SQL_Connection.CreateCommand();
                    cmd.CommandText = "SELECT count(*) FROM [Audience]";
                    DataTable dataTable = null;
                    try
                    {
                        da = new SqlDataAdapter(cmd);
                        dataTable = new DataTable();
                        dataTable.Locale = CultureInfo.InvariantCulture;
                        SQL_Connection.Open();
                        // Fill the data table with select statement's query results:
                        int recordsAffected = da.Fill(dataTable);
                        if (recordsAffected > 0)
                        {
                            foreach (DataRow reader in dataTable.Rows)
                            {
                                number = Convert.ToInt32(reader[0]);
                            }
                        }
                    }
                    catch (SqlException)
                    {
                        return -2;
                    }
                    finally
                    {
                        if (da != null)
                            da.Dispose();
                        if (dataTable != null)
                            dataTable.Dispose();
                    }
                }
                return number;
            }
        }
    }
}